import pandas as pd
# Read GBP LIBOR historical data
df = pd.read_csv("LIBOR GBP.csv")
df.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 9600 entries, 0 to 9599 Data columns (total 9 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 Date 9600 non-null object 1 Week day 9600 non-null object 2 ON 5307 non-null float64 3 1W 6083 non-null float64 4 1M 9409 non-null float64 5 2M 8842 non-null float64 6 3M 9600 non-null float64 7 6M 9409 non-null float64 8 12M 9095 non-null float64 dtypes: float64(7), object(2) memory usage: 675.1+ KB
# Extract data where both '3M' and '6M' columns are not null
df_libor = df[df['3M'].notna() & df['6M'].notna()]
# Include only the maturities we want
df_libor = df_libor[['Date', '3M', '6M']].reset_index(drop=True)
# Reformat the 'Date' column to a standard date format
df_libor['Date'] = pd.to_datetime(df_libor['Date'], format='%d.%m.%Y')
df_libor.set_index('Date', inplace=True)
df_libor.head()
| 3M | 6M | |
|---|---|---|
| Date | ||
| 2023-03-31 | 4.4180 | 4.7447 |
| 2023-03-30 | 4.4129 | 4.7189 |
| 2023-03-29 | 4.4042 | 4.7094 |
| 2023-03-28 | 4.4119 | 4.7307 |
| 2023-03-27 | 4.3888 | 4.6853 |
import plotly.express as px
# Create a line plot using Plotly
fig = px.line(df_libor, x=df_libor.index, y=['3M', '6M'],
labels={'Date': 'Date', 'value': 'Interest Rates (%)'},
title='Historical Trend of 3-Month and 6-Month GBP LIBOR Rates',
color_discrete_sequence=["dodgerblue", "orange"])
# Customize the appearance
fig.update_traces(mode='lines', line=dict(width=2))
fig.update_layout(showlegend=True, legend_title_text='Maturity')
fig.update_yaxes(title_text='Interest Rates (%)')
# Show the interactive plot
fig.show()
# Set time parameters for the interest-rate swap (IRS)
start_date = "2000-01-01" # Start of a quarter
tenure = 4 # Tenure (Years)
# Set other parameters for IRS
notional_amt = 100000 # Notional amount (GBP)
fixed_rate = 7 # Fixed-interest rate (%)
spread = 2 # Spread added to LIBOR (%)
# Aggregate interest-rate data to quarterly intervals
df_libor_quarterly = df_libor.resample('Q').mean()
# Create a DataFrame to store cash flows, using same quarterly intervals
df_cash_flow = pd.DataFrame({'Date': df_libor_quarterly.index})
df_cash_flow.set_index('Date', inplace=True)
# Add LIBOR interest rate as a column
df_cash_flow['libor_3m'] = df_libor_quarterly['3M']
# Calculate floating payments & fixed payments for each quarter
df_cash_flow["floating_payment"] = (notional_amt * (df_cash_flow['libor_3m'] + spread) / 100) * (1/4)
df_cash_flow["fixed_payment"] = (notional_amt * fixed_rate / 100) * (1/4)
df_cash_flow["net_cash_flow"] = df_cash_flow["fixed_payment"] - df_cash_flow["floating_payment"]
# Calculate the end date of the contract period
end_date = pd.to_datetime(start_date) + pd.DateOffset(years=tenure)
# Create a subset of payments during contract period (from start_date to end_date)
df_contract_cash_flows = df_cash_flow[(df_cash_flow.index >= start_date) & (df_cash_flow.index <= end_date)]
df_contract_cash_flows.head(5)
| libor_3m | floating_payment | fixed_payment | net_cash_flow | |
|---|---|---|---|---|
| Date | ||||
| 2000-03-31 | 6.203612 | 2050.902930 | 1750.0 | -300.902930 |
| 2000-06-30 | 6.273138 | 2068.284590 | 1750.0 | -318.284590 |
| 2000-09-30 | 6.205623 | 2051.405742 | 1750.0 | -301.405742 |
| 2000-12-31 | 6.076402 | 2019.100437 | 1750.0 | -269.100437 |
| 2001-03-31 | 5.715821 | 1928.955312 | 1750.0 | -178.955312 |
fixed_payment - floating_payment) has also been added.# Create a subset of interest-rates during contract period (from start_date to end_date)
df_contract_libor_quarterly = df_libor_quarterly[(df_libor_quarterly.index >= start_date) & (df_libor_quarterly.index <= end_date)]
# Create a line plot using Plotly
fig = px.line(df_contract_libor_quarterly, x=df_contract_libor_quarterly.index, y='3M',
title='GBP LIBOR 3-Month Rate Trends During Swap Contract')
# Customize the appearance
fig.update_traces(mode='lines', line=dict(width=3))
fig.update_yaxes(title_text='Interest Rate (%)')
# Show the interactive plot
fig.show()
# Create a line plot for net cash flow using Plotly
fig = px.line(df_contract_cash_flows, x=df_contract_cash_flows.index, y=['floating_payment', 'fixed_payment'],
title='Timeline of Fixed vs. Floating Payments<br><sup>Assuming Quarterly Payment Intervals</sup>',
color_discrete_sequence=["dodgerblue", "orange"])
# Customize the appearance
fig.update_traces(mode='lines', line=dict(width=3))
fig.update_layout(showlegend=True, legend_title_text='Payment Type')
fig.update_yaxes(title_text='Payment (£)')
# Show the interactive plot
fig.show()
import plotly.graph_objs as go
# Split the data into positive and negative series
positive_cash_flow = df_contract_cash_flows['net_cash_flow'].apply(lambda x: x if x > 0 else None)
negative_cash_flow = df_contract_cash_flows['net_cash_flow'].apply(lambda x: x if x < 0 else None)
# Create the line plot
fig = go.Figure()
# Trace for positive cash flow
fig.add_trace(go.Scatter(x=df_contract_cash_flows.index, y=positive_cash_flow,
mode='lines', line=dict(width=3, color='green'),
fill='tozeroy', fillcolor='rgba(0, 255, 0, 0.5)', name='Positive Cash Flow'))
# Trace for negative cash flow
fig.add_trace(go.Scatter(x=df_contract_cash_flows.index, y=negative_cash_flow,
mode='lines', line=dict(width=3, color='red'),
fill='tozeroy', fillcolor='rgba(255, 0, 0, 0.5)', name='Negative Cash Flow'))
# Add a horizontal line at zero
fig.add_shape(type="line", x0=df_contract_cash_flows.index.min(), x1=df_contract_cash_flows.index.max(),
y0=0, y1=0, line=dict(color="darkgrey", width=3))
# Customize the layout
fig.update_layout(title='Timeline of Net Cash Flow<br><sup>Net Cash Flow = Fixed Payment - Floating Payment</sup>',
yaxis_title='Cash Flow (£)')
# Show the plot
fig.show()
Positive Cash Flow: This occurs when the company paying a fixed interest rate (e.g., Company A) profits. It happens when the floating interest rate, which this company receives, is higher than the fixed rate it pays. In such cases, Company A earns more from the floating rate than its fixed-rate payments, resulting in a net gain or positive cash flow.
Negative Cash Flow: This happens when the floating interest rate falls below the fixed rate. In this scenario, Company A, which is committed to a fixed interest rate, ends up paying more than it receives from the floating rate. This mismatch leads to a financial loss, or negative cash flow.